package com.zyb.lmbackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyb.lmbackend.entity.GoodsBackClassified;
import java.time.LocalDateTime;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface GoodsBackClassifiedMapper extends BaseMapper<GoodsBackClassified> {

    @Insert(
            "/* UPSERT 当前版本的分类结果，按置信度更高者覆盖 */ " +
            "INSERT INTO goods_back_classified (sn, back_detail, category_code, confidence, llm_reason, model_name, task_version, classified_at) " +
            "VALUES (#{sn}, #{backDetail}, #{categoryCode}, #{confidence}, #{llmReason}, #{modelName}, #{taskVersion}, NOW()) " +
            "ON DUPLICATE KEY UPDATE " +
            "back_detail = VALUES(back_detail), " +
            "category_code = IF(VALUES(confidence) > confidence, VALUES(category_code), category_code), " +
            "confidence = IF(VALUES(confidence) > confidence, VALUES(confidence), confidence), " +
            "llm_reason = IF(VALUES(confidence) > confidence, VALUES(llm_reason), llm_reason), " +
            "model_name = IF(VALUES(confidence) > confidence, VALUES(model_name), model_name), " +
            "classified_at = IF(VALUES(confidence) > confidence, NOW(), classified_at)"
    )
    int upsert(@Param("sn") String sn,
               @Param("backDetail") String backDetail,
               @Param("categoryCode") String categoryCode,
               @Param("confidence") double confidence,
               @Param("llmReason") String llmReason,
               @Param("modelName") String modelName,
               @Param("taskVersion") int taskVersion);

    @Select("/* 取某类目的高置信样例 */ SELECT c.sn, c.back_detail AS backDetail FROM goods_back_classified c WHERE c.category_code = #{code} ORDER BY c.confidence DESC LIMIT #{limit}")
    List<GoodsBackClassified> selectTopExamples(@Param("code") String code, @Param("limit") int limit);

    @Select("/* 从视图读取指定版本的类目计数 */ SELECT s.category_code AS code, d.name AS name, s.cnt AS count\n" +
            "FROM v_category_stats s JOIN category_dict d ON d.code = s.category_code\n" +
            "WHERE s.task_version = #{version}")
    List<com.zyb.lmbackend.dto.CategoryStatDto> selectStatsFromView(@Param("version") int version);

    @Select({
            "<script>",
            "/* 按时间范围/版本从明细表聚合类目计数 */",
            "SELECT c.category_code AS code, d.name AS name, COUNT(*) AS count",
            "FROM goods_back_classified c JOIN category_dict d ON d.code = c.category_code",
            "<where>",
            "  <if test='version != null'> AND c.task_version = #{version} </if>",
            "  <if test='from != null'> AND c.classified_at &gt;= #{from} </if>",
            "  <if test='to != null'> AND c.classified_at &lt;= #{to} </if>",
            "</where>",
            "GROUP BY c.category_code, d.name",
            "</script>"
    })
    List<com.zyb.lmbackend.dto.CategoryStatDto> selectStatsFromTable(@Param("version") Integer version,
                                                                      @Param("from") LocalDateTime from,
                                                                      @Param("to") LocalDateTime to);
}
